from dbutils.pooled_db import PooledDB  # 连接池
import pymysql


# MYSQL连接池
class MYSQL_CZ:  # mysql操作
    # close_all_connections() 关闭所有连接,不推荐使用.这个连接是自动关闭的
    __host = '106.14.158.84'
    __port = 3306
    __database = 'pa_chong'
    __user = 'a18222970'
    __password = 'cx123100'

    # 创建一个PooledDB实例，它将创建一个连接池
    __pool = PooledDB(
        creator=pymysql,  # 使用pymysql数据库连接
        host=__host,
        user=__user,
        password=__password,
        database=__database,
        port=__port,
        maxconnections=6,  # 连接池允许的最大连接数
        mincached=3,  # 初始化时，连接池中至少创建的空闲连接数
        maxcached=6,  # 连接池中最多缓存多少个空闲连接
        maxusage=None,  # 一个连接最多被重复使用的次数，None表示无限制
        blocking=True,  # 连接池用完时，请求连接是否阻塞
        setsession=[],  # 开始会话前执行的命令列表
        ping=4  # ping MySQL服务端，检查是否存活
    )

    @classmethod
    def get_connection(cls):
        """从连接池获取一个连接"""
        return cls.__pool.connection()

    def cha_zhao(self):
        """执行查询操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        # select 字段(*) from 表 条件
        sql = """
        select * from zu_fang where zhua_qu_shi_jian > 1726043431
        """
        try:
            """ fetchone
            cursor.execute(sql)
            # fetchone 返回1条数据,调用1次向后返回1条数据
            # result = cursor.fetchone()
            while True:
                result = cursor.fetchone()
                if result:
                    print(result)
                else:
                    break
            """
            """
            cursor.execute(sql)
            # fetchall 1次性返回全部结果
            results = cursor.fetchall()
            for result in results:
                print(result)
            """

            cursor.execute(sql)
            # fetchmany 1次性返回指定条数数据(2)结果
            results = cursor.fetchmany(2)
            for result in results:
                print(result)

        except pymysql.Error as e:
            print(f"查询失败: {e}")
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

    # 查找租房数据表中保存的图片名
    def cha_zhao_tpm(self,tu_pian_bao_cun):
        """执行查询操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        # select 字段(*) from 表 条件
        sql = """
        SELECT tu_pian_bao_cun FROM zu_fang WHERE tu_pian_bao_cun LIKE %s
        """
        try:
            cursor.execute(sql,(tu_pian_bao_cun,))
            # fetchall 1次性返回全部结果
            results = cursor.fetchall()
        except pymysql.Error as e:
            print(f"查询失败: {e}")
            results = None
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

        return results

    """
    查找租房表里相同的图片名和相同房租价格的数据
    参数:
    tu_pian_bao_cun_c: 要查找的图片名
    jia_ge_c:          查找的房租价格
    返回: tu_pian_bao_cun, jia_ge, zhua_qu_shi_jian
    """
    def cha_xun_tp_jg(self,tu_pian_bao_cun_c,jia_ge_c):
        """执行查询操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        try:
            # sql = "SELECT tu_pian_bao_cun, jia_ge, zhua_qu_shi_jian FROM zu_fang WHERE tu_pian_bao_cun LIKE %s"
            sql = "SELECT tu_pian_bao_cun, jia_ge, zhua_qu_shi_jian FROM zu_fang WHERE tu_pian_bao_cun LIKE %s AND jia_ge = %s"
            cursor.execute(sql, (tu_pian_bao_cun_c,jia_ge_c,))
            results = cursor.fetchall() # fetchall方法返回的是一个元组组成的列表

        except pymysql.Error as e:
            print(f"查询失败: {e}")
            results = None
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

        return results


    def zeng_jia(self, ping_fang, gong_si, biao_ti, zhua_qu_shi_jian):
        """执行插入操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        sql = """
            INSERT INTO zu_fang(ping_fang, gong_si, biao_ti, zhua_qu_shi_jian)
            VALUES (%s, %s, %s, %s)
        """
        try:
            cursor.execute(sql, (ping_fang, gong_si, biao_ti, zhua_qu_shi_jian))
            conn.commit()
        except pymysql.Error as e:
            print(f"插入失败: {e}")
            # 所有在当前事务中进行的更改都会被撤销
            conn.rollback()
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

    """
    往 zu_fang 表里添加一条数据,参数:
    tu_pian_di_zhi : 图片的网络地址
    tu_pian_bao_cun: 图片的本地保存的名字,带扩展名
    ping_fang:       租房的面积
    gong_si:         发布信息的中介公司
    biao_ti:         发布的标题
    jia_ge:          房租价格
    fa_bu_ri_qi:     发布日期
    lian_jie:        租房信息具体页面地址
    zhua_qu_shi_jian:抓取信息的时间,以时间戳保存
    """
    def zj_zu_fang_tiao_mu(self, tu_pian_di_zhi, tu_pian_bao_cun, ping_fang, gong_si, biao_ti, jia_ge, fa_bu_ri_qi,
                         lian_jie, zhua_qu_shi_jian):
        """执行插入操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        sql = """
            INSERT INTO zu_fang(tu_pian_di_zhi, tu_pian_bao_cun, ping_fang, gong_si,biao_ti,jia_ge,fa_bu_ri_qi,lian_jie,zhua_qu_shi_jian)
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s)
        """
        try:
            cursor.execute(sql, (
            tu_pian_di_zhi, tu_pian_bao_cun, ping_fang, gong_si, biao_ti, jia_ge, fa_bu_ri_qi, lian_jie,
            zhua_qu_shi_jian,))
            conn.commit()
        except pymysql.Error as e:
            print(f"插入失败: {e}")
            # 所有在当前事务中进行的更改都会被撤销
            conn.rollback()
            return -1
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池
            return 0


    """
    =============     二手房     =======================
    """

    """
    查询mai_fang表中信息标题与房屋卖价都相同的数据
    参数:
    sf_biaoTi: 信息标题
    sf_mianJi: 房屋卖价
    返回:售房标题, 售房卖价, 抓取时间
    """
    def cx_er_shou_fang_bt_sj(self,sf_biaoTi,sf_maiJi):
        """执行查询操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        try:
            # sql = "SELECT tu_pian_bao_cun, jia_ge, zhua_qu_shi_jian FROM zu_fang WHERE tu_pian_bao_cun LIKE %s"
            sql = "SELECT 售房标题, 售房卖价, 抓取时间 FROM mai_fang WHERE 售房标题 LIKE %s AND 售房卖价 = %s"
            cursor.execute(sql, (sf_biaoTi,sf_maiJi,))
            results = cursor.fetchall() # fetchall方法返回的是一个元组组成的列表

        except pymysql.Error as e:
            print(f"查询失败: {e}")
            results = None
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

        return results

    def cx_xiao_qu_shi_jian_cuo(self):
        """执行查询操作，获取id最大对应的抓取时间"""
        conn = self.get_connection()
        cursor = conn.cursor()
        try:
            # 使用 MAX 函数来获取 抓取时间 字段的最大值
            sql = "SELECT MAX(时间) FROM er_shou_fang_jun_jia"
            cursor.execute(sql)
            result = cursor.fetchone()  # fetchone方法返回的是单行结果
            result = result[0]

        except pymysql.Error as e:
            print(f"查询失败: {e}")
            result = None
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

        return result

    def cx_xiao_qu_jj(self,shiJian,junJia):
        """执行查询操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        try:
            # sql = "SELECT tu_pian_bao_cun, jia_ge, zhua_qu_shi_jian FROM zu_fang WHERE tu_pian_bao_cun LIKE %s"
            sql = "SELECT 小区 FROM er_shou_fang_jun_jia WHERE 时间 = %s AND 均价 = %s"
            cursor.execute(sql, (shiJian,junJia,))
            result = cursor.fetchone() # fetchone方法返回的是单行结果果

        except pymysql.Error as e:
            print(f"查询失败: {e}")
            result = None
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

        return result

    """
        往 zu_fang 表里添加一条数据,参数:
        售房面积, 售房卖价, 售房平方均价, 售房信息连接, 售房标题, 售房地址, 建造年代, 抓取时间
    """

    def zj_er_shou_fang_tiao_mu(self, sf_mianJi, sf_mai_jia, sf_jun_jia, xx_lian_jie, sf_biaoTi, sf_di_zhi, sf_nian_dai,
                           zhua_qu_shi_jian):
        """执行插入操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        sql = """
                INSERT INTO mai_fang(售房面积, 售房卖价, 售房平方均价, 售房信息连接, 售房标题, 售房地址, 建造年代, 抓取时间)
                VALUES (%s,%s,%s,%s,%s,%s,%s,%s)
            """
        try:
            cursor.execute(sql, (
                sf_mianJi, sf_mai_jia, sf_jun_jia, xx_lian_jie, sf_biaoTi, sf_di_zhi, sf_nian_dai, zhua_qu_shi_jian,))
            conn.commit()
        except pymysql.Error as e:
            print(f"插入失败: {e}")
            # 所有在当前事务中进行的更改都会被撤销
            conn.rollback()
            return -1
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池
            return 0

    """
    增加小区平均价格数据
    """
    def zj_xiao_qu_jun_jia(self, zhua_qu_shi_jian, xq_jun_jia, xiao_qu):
        """执行插入操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        sql = """
                INSERT INTO er_shou_fang_jun_jia(时间, 均价, 小区) VALUES (%s,%s,%s)
            """
        try:
            cursor.execute(sql, (zhua_qu_shi_jian, xq_jun_jia, xiao_qu,))
            conn.commit()
        except pymysql.Error as e:
            print(f"插入失败: {e}")
            # 所有在当前事务中进行的更改都会被撤销
            conn.rollback()
            return -1
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池
            return 0

    # 删除
    def shan_chu(self):
        """执行删除操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        sql = """
        DELETE FROM zu_fang WHERE ping_fang = '43.16'
        """
        try:
            cursor.execute(sql)
            conn.commit()
        except pymysql.Error as e:
            print(f"删除失败: {e}")
            # rollback 所有在当前事务中进行的更改都会被撤销
            conn.rollback()
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

    # 清空zu_fang表
    def qing_kong_zu_fang(self):
        """执行删除操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        sql = """
        DELETE FROM zu_fang
        """
        try:
            cursor.execute(sql)
            conn.commit()
        except pymysql.Error as e:
            print(f"删除失败: {e}")
            # rollback 所有在当前事务中进行的更改都会被撤销
            conn.rollback()
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

    # 清空mai_fang表
    def qing_kong_mai_fang(self):
        """执行删除操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        sql = """
        DELETE FROM mai_fang
        """
        try:
            cursor.execute(sql)
            conn.commit()
        except pymysql.Error as e:
            print(f"删除失败: {e}")
            # rollback 所有在当前事务中进行的更改都会被撤销
            conn.rollback()
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池


    """
    清空指定表里的全部数据
    参数:
    biao: 要清空的表名
    """
    def qing_kong_biao(self,biao):
        """执行删除操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        sql = f"""
        DELETE FROM {biao}
        """
        try:
            cursor.execute(sql)
            conn.commit()
        except pymysql.Error as e:
            print(f"删除失败: {e}")
            # rollback 所有在当前事务中进行的更改都会被撤销
            conn.rollback()
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

    def geng_xin(self):
        """执行更新操作"""
        conn = self.get_connection()
        cursor = conn.cursor()
        sql = """
        UPDATE zu_fang SET biao_ti = '绝代风华' WHERE zhua_qu_shi_jian = 12549988776644123
        """
        try:
            cursor.execute(sql)
            conn.commit()
        except pymysql.Error as e:
            print(f"更新失败: {e}")
            # rollback 所有在当前事务中进行的更改都会被撤销
            conn.rollback()
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

    def zj(self,ccxx,aass):
        conn = self.get_connection()
        cursor = conn.cursor()
        sql = """
                    INSERT INTO cs(ccxx, aass)
                    VALUES (%s, %s)
                """
        try:
            cursor.execute(sql, (ccxx, aass))
            conn.commit()
        except pymysql.Error as e:
            print(f"插入失败: {e}")
            # 所有在当前事务中进行的更改都会被撤销
            conn.rollback()
        finally:
            cursor.close()
            conn.close()  # 关闭连接，实际上是将连接返回到连接池

    """  =================  外部修改获取类私有属性方法 ================"""

    def get_database(self):
        """获取当前__database的值"""
        return self.__database

    def set_database(self, database):
        """设置__database的值"""
        if database:
            self.__database = database

    def get_host(self):
        return self.__host

    def set_host(self, host):
        if host:
            self.__host = host

    def get_user(self):
        return self.__user

    def set_user(self, user):
        if user:
            self.__user = user

    def get_password(self):
        return self.__password

    def set_password(self, password):
        if password:
            self.__password = password


class MYSQL_PT:
    __host = '106.14.158.84'
    __port = 3306
    __database = 'pa_chong'
    __user = 'a18222970'
    __password = 'cx123100'  # 私有类变量

    def __init__(self):
        self.__conn = None
        self.__cursor = None
        self.connect_db()  # 初始化时建立数据库连接


    def __del__(self):
        self.close_db()  # 类销毁时关闭数据库连接

    def connect_db(self):
        """连接数据库"""
        try:
            self.__conn = pymysql.connect(
                host=self.__host,
                user=self.__user,
                password=self.__password,
                database=self.__database,
                port=self.__port
            )
            self.__cursor = self.__conn.cursor()
        except pymysql.Error as e:
            print(f"数据库连接失败: {e}")

    def close_db(self):
        """关闭数据库连接"""
        if self.__cursor:
            self.__cursor.close()
        if self.__conn:
            self.__conn.close()

    #查询大于参数id的tu_pian_bao_cun字段所有数据,
    def cha_xun_01(self,id):
        """执行查询操作"""
        try:
            sql = "SELECT tu_pian_bao_cun FROM zu_fang WHERE id > %s"
            self.__cursor.execute(sql, (id,))
            results = self.__cursor.fetchall() # fetchall方法返回的是一个元组组成的列表
            #print(results)
            return results
        except pymysql.Error as e:
            print(f"查询失败: {e}")

    def cha_xun_02(self,tu_pian_bao_cun_c,jia_ge_c):
        """执行查询操作"""
        try:
            # sql = "SELECT tu_pian_bao_cun, jia_ge, zhua_qu_shi_jian FROM zu_fang WHERE tu_pian_bao_cun LIKE %s"
            sql = "SELECT tu_pian_bao_cun, jia_ge, zhua_qu_shi_jian FROM zu_fang WHERE tu_pian_bao_cun LIKE %s AND jia_ge = %s"
            self.__cursor.execute(sql, (tu_pian_bao_cun_c,jia_ge_c,))
            results = self.__cursor.fetchall() # fetchall方法返回的是一个元组组成的列表
            return results


        except pymysql.Error as e:
            print(f"查询失败: {e}")























